
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 09/28/2015 14:43:08
-- Generated from EDMX file: E:\Iospro\IosWeb\IOSpro\IosDatamodel\IosDatamodel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [IosDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Comments_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_Comments_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Comments_Videos]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_Comments_Videos];
GO
IF OBJECT_ID(N'[dbo].[FK_UserReplys_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserReplys] DROP CONSTRAINT [FK_UserReplys_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Videos_VideoSheet]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Videos] DROP CONSTRAINT [FK_Videos_VideoSheet];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Comments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comments];
GO
IF OBJECT_ID(N'[dbo].[ProductQR]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductQR];
GO
IF OBJECT_ID(N'[dbo].[UserReplys]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserReplys];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[Videos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Videos];
GO
IF OBJECT_ID(N'[dbo].[VideoSheet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VideoSheet];
GO
IF OBJECT_ID(N'[dbo].[VideoQRString]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VideoQRString];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Comments'
CREATE TABLE [dbo].[Comments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Email] nvarchar(50)  NOT NULL,
    [UserComment] varchar(max)  NULL,
    [VideoId] int  NOT NULL,
    [CommentDate] datetime  NOT NULL
);
GO

-- Creating table 'ProductQR'
CREATE TABLE [dbo].[ProductQR] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [QRString] varchar(50)  NOT NULL
);
GO

-- Creating table 'UserReplys'
CREATE TABLE [dbo].[UserReplys] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Email] nvarchar(50)  NOT NULL,
    [UserCommentId] int  NOT NULL,
    [Reply] varchar(max)  NULL,
    [ReplyDate] datetime  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Email] nvarchar(50)  NOT NULL,
    [PassWord] nchar(50)  NOT NULL,
    [Verification] nchar(10)  NULL,
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Videos'
CREATE TABLE [dbo].[Videos] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [VideoTitle] nvarchar(50)  NULL,
    [VideoImage] nvarchar(50)  NULL,
    [DateTime] datetime  NOT NULL,
    [VideoIntroduction] varchar(max)  NOT NULL,
    [VideoIssue] nvarchar(50)  NOT NULL,
    [VideoSheetId] int  NOT NULL,
    [VideoCode] varchar(50)  NULL
);
GO

-- Creating table 'VideoSheet'
CREATE TABLE [dbo].[VideoSheet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Image] nvarchar(50)  NULL,
    [Text] varchar(max)  NULL,
    [VideoUrlName] varchar(50)  NOT NULL
);
GO

-- Creating table 'VideoQRString'
CREATE TABLE [dbo].[VideoQRString] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [videoqr] varchar(50)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [PK_Comments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProductQR'
ALTER TABLE [dbo].[ProductQR]
ADD CONSTRAINT [PK_ProductQR]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserReplys'
ALTER TABLE [dbo].[UserReplys]
ADD CONSTRAINT [PK_UserReplys]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Email] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Email] ASC);
GO

-- Creating primary key on [Id] in table 'Videos'
ALTER TABLE [dbo].[Videos]
ADD CONSTRAINT [PK_Videos]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VideoSheet'
ALTER TABLE [dbo].[VideoSheet]
ADD CONSTRAINT [PK_VideoSheet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VideoQRString'
ALTER TABLE [dbo].[VideoQRString]
ADD CONSTRAINT [PK_VideoQRString]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Email] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_Comments_Users]
    FOREIGN KEY ([Email])
    REFERENCES [dbo].[Users]
        ([Email])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comments_Users'
CREATE INDEX [IX_FK_Comments_Users]
ON [dbo].[Comments]
    ([Email]);
GO

-- Creating foreign key on [VideoId] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_Comments_Videos]
    FOREIGN KEY ([VideoId])
    REFERENCES [dbo].[Videos]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comments_Videos'
CREATE INDEX [IX_FK_Comments_Videos]
ON [dbo].[Comments]
    ([VideoId]);
GO

-- Creating foreign key on [Email] in table 'UserReplys'
ALTER TABLE [dbo].[UserReplys]
ADD CONSTRAINT [FK_UserReplys_Users]
    FOREIGN KEY ([Email])
    REFERENCES [dbo].[Users]
        ([Email])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserReplys_Users'
CREATE INDEX [IX_FK_UserReplys_Users]
ON [dbo].[UserReplys]
    ([Email]);
GO

-- Creating foreign key on [VideoSheetId] in table 'Videos'
ALTER TABLE [dbo].[Videos]
ADD CONSTRAINT [FK_Videos_VideoSheet]
    FOREIGN KEY ([VideoSheetId])
    REFERENCES [dbo].[VideoSheet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Videos_VideoSheet'
CREATE INDEX [IX_FK_Videos_VideoSheet]
ON [dbo].[Videos]
    ([VideoSheetId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------